MySQL stores DECIMAL values as exact numeric data using precise decimal representation, while FLOAT and DOUBLE use approximate floating-point binary formats. This results in differences in storage, precision, and accuracy.

How MySQL Handles DECIMAL(M, D) Internally

1
DECIMAL uses exact decimal storage with base-10 representation.
2
M = total digits; D = digits after the decimal point.
3
Digits are packed in groups of 9, stored in 4-byte chunks for efficiency.
4
No binary rounding errors; values remain exact.
5
Ideal for financial calculations requiring accuracy.

How FLOAT and DOUBLE Are Stored

1
Use IEEE 754 floating-point binary representation.
2
FLOAT uses 4 bytes; DOUBLE uses 8 bytes.
3
Values are stored in binary (base-2), not base-10.
4
Cannot store many decimals exactly (e.g., 0.1).
5
Faster but less precise due to approximation.

Key Differences in Storage and Accuracy

1
DECIMAL stores exact values; FLOAT/DOUBLE store approximate values.
2
DECIMAL storage varies with digit count; FLOAT/DOUBLE have fixed size.
3
FLOAT/DOUBLE offer speed and range; DECIMAL offers precision.
4
DECIMAL is preferred for financial data; FLOAT/DOUBLE for scientific computations.

Difficulty: 5/10

Topics: decimal-precision, floating-point-accuracy, storage-format

Scenario Questions

0-2 years experience

1
You're adding a 'price' column to an orders table. The product manager says prices range from $0.01 to $999,999.99 with two decimal places. What column definition would you use and why?
2
A teammate inserted 123.456 into a DECIMAL(5,2) column. What value actually gets stored, and does MySQL warn you?
3
You see a query doing WHERE float_col = 1.23. Why might this fail to match rows that look correct in a SELECT, and how would you fix it?

2-5 years experience

1
We're migrating a legacy billing table that used FLOAT for dollar amounts. Customers are reporting penny discrepancies on invoices. Walk me through how you'd diagnose and fix this without downtime.
2
An analytics query aggregates millions of rows with SUM(double_col) and the total drifts by cents each run. The business needs exact totals. What are your options, and what's the performance tradeoff?
3
You're designing a schema for an e-commerce platform that handles multiple currencies. Some need 2 decimal places, others 0 (JPY), others 3 (BHD). How do you model this cleanly?

5-8 years experience

1
Our high-throughput trading engine currently uses DOUBLE for position P&L calculations. At peak we see 50k writes/sec. The risk team now requires exact decimal results for regulatory reporting. How do you evaluate migrating to DECIMAL without killing throughput?
2
A distributed saga updates account balances across three services. Each service uses DECIMAL(19,4) locally. During reconciliation, the sums don't match due to rounding at different steps. How do you design a consistent rounding strategy across services?
3
We're building a time-series database for IoT sensor data (temperature, pressure). The sensors report 3 decimal places but we only need 1 for dashboards. Storage cost is a concern. What numeric type and compression approach would you recommend?

8+ years experience

1
The company acquired a fintech startup whose core ledger uses FLOAT for all monetary columns. You have 18 months to migrate to exact arithmetic before SOC2 audit. The system processes $50B/year with zero-downtime requirements. Outline your migration strategy, including how you'll validate correctness at scale.
2
Three product teams independently chose numeric types for 'amount' fields: Team A uses DECIMAL(10,2), Team B uses DECIMAL(19,4), Team C uses BIGINT storing cents. Cross-team reporting is breaking. How do you establish a company-wide standard and drive adoption without blocking feature work?
3
You're designing a new financial platform that must support crypto (up to 18 decimals), fiat (2-4 decimals), and synthetic assets with dynamic precision. The schema must evolve for 10+ years. What abstraction layer do you build above the storage engine to handle precision policy changes without data migration?

Follow-up Questions

❓What happens if you insert 999.999 into DECIMAL(5,2)?
❓Why does SELECT 0.1 + 0.2 = 0.3 return false with DOUBLE?
❓How would you store currency exchange rates that need 6 decimal places?